package cmpe273.proj1.dao;

import static cmpe273.proj1.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import cmpe273.proj1.model.Course;

/**
 * This class represents a concrete JDBC implementation of the {@link CourseDAO} interface.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class CourseDAOJDBC implements CourseDAO {

	// Constants ----------------------------------------------------------------------------------
	private static final String SQL_FIND_BY_ID =
			"SELECT Course_ID, Name, Section, MeetingTime, Location FROM course WHERE Course_ID = ? LIMIT 1";
	private static final String SQL_FIND_BY_NAME_SECTION =
			"SELECT Course_ID, Name, Section, MeetingTime, Location FROM course WHERE Name = ? AND Section = ? LIMIT 1";
	private static final String SQL_FIND_BY_GENERIC =
			"SELECT Course_ID, Name, Section, MeetingTime, Location FROM course";
	private static final String SQL_LIST_ORDER_BY_ID =
			"SELECT Course_ID, Name, Section, MeetingTime, Location FROM course ORDER BY Course_ID";
	private static final String SQL_INSERT =
			"INSERT INTO course (Name, Section, MeetingTime, Location) VALUES (?, ?, ?, ?)";
	private static final String SQL_UPDATE =
			"UPDATE course SET  Name = ?, Section = ?, MeetingTime = ?, Location = ? WHERE Course_ID = ?";
	private static final String SQL_DELETE =
			"DELETE FROM course WHERE Course_ID = ?";

	// Vars ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors -------------------------------------------------------------------------------

	/**
	 * Construct an Course DAO for the given DAOFactory. Package private so that it can be constructed
	 * inside the DAO package only.
	 * @param daoFactory The DAOFactory to construct this Course DAO for.
	 */
	CourseDAOJDBC(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// Actions ------------------------------------------------------------------------------------

	@Override
	public Course find(Long id) throws DAOException {
		return find(SQL_FIND_BY_ID, id);
	}

	@Override
	public Course find(String name, String section) throws DAOException {
		return find(SQL_FIND_BY_NAME_SECTION, name, section);
	}

	/**
	 * Returns the course from the database matching the given SQL query with the given values.
	 * @param sql The SQL query to be executed in the database.
	 * @param values The PreparedStatement values to be set.
	 * @return The course from the database matching the given SQL query with the given values.
	 * @throws DAOException If something fails at database level.
	 */
	private Course find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Course course = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				course = map(resultSet);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return course;
	}

	@Override
	public List<Course> list() throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Course> courses = new ArrayList<Course>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_LIST_ORDER_BY_ID, false);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				courses.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return courses;
	}

	@Override
	public List<Course> search(Course searchParams) throws DAOException {
		//prepare
		StringBuilder query = new StringBuilder(SQL_FIND_BY_GENERIC);
		HashMap<String, Object> queryValues = new HashMap<String, Object>();

		//build query
		if(searchParams.getId() != null && searchParams.getId() > 0)
			queryValues.put(" Course_ID = ?", searchParams.getId());
		if(!searchParams.getCourseName().isEmpty())
			queryValues.put(" Name LIKE ?", wrapLikeCompare(searchParams.getCourseName(), false));
		if(!searchParams.getSection().isEmpty())
			queryValues.put(" Section LIKE ?", wrapLikeCompare(searchParams.getSection(), false));
		if(!searchParams.getTimes().isEmpty())
			queryValues.put(" MeetingTime LIKE ?", wrapLikeCompare(searchParams.getTimes(), true));
		if(!searchParams.getLocation().isEmpty())
			queryValues.put(" Location LIKE ?", wrapLikeCompare(searchParams.getLocation(), true));

		if(queryValues.size() == 0)
			return list();

		//transmute
		Object[] values = new Object[queryValues.size()];
		query.append(" WHERE");

		int idx = 0;
		for(Entry<String, Object> entry : queryValues.entrySet()) {
			if(idx > 0)
				query.append(" AND");
			query.append(entry.getKey());
			values[idx++] = entry.getValue();
		}
		query.append(String.format(" ORDER BY Course_ID LIMIT %d", DAOUtil.MAX_ROWS));

		//Copy-paste from find(...) and list()
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Course> courses = new ArrayList<Course>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, query.toString(), false, values);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				courses.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return courses;
		//end Copy-past from find(...) and list()
	}

	@Override
	public void create(Course course) throws IllegalArgumentException, DAOException {
		if (course.getId() != null) {
			throw new IllegalArgumentException("Course is already created, the course ID is not null.");
		}

		Object[] values = {
				course.getCourseName(),
				course.getSection(),
				course.getTimes(),
				course.getLocation()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, true, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Creating course failed, no rows affected.");
			}
			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next()) {
				course.setId(generatedKeys.getLong(1));
			} else {
				throw new DAOException("Creating course failed, no generated key obtained.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}

	@Override
	public void update(Course course) throws DAOException {
		if (course.getId() == null) {
			throw new IllegalArgumentException("Course is not created yet, the course ID is null.");
		}

		Object[] values = {
				course.getCourseName(),
				course.getSection(),
				course.getTimes(),
				course.getLocation(),
				course.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Updating course failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public void delete(Course course) throws DAOException {
		Object[] values = { 
				course.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Deleting course failed, no rows affected.");
			} else {
				course.setId(null);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	// Helpers ------------------------------------------------------------------------------------

	/**
	 * Map the current row of the given ResultSet to an Course.
	 * @param resultSet The ResultSet of which the current row is to be mapped to an Course.
	 * @return The mapped Course from the current row of the given ResultSet.
	 * @throws SQLException If something fails at database level.
	 */
	private static Course map(ResultSet resultSet) throws SQLException {
		Course c = new Course();
		c.setId(resultSet.getLong("Course_ID"));
		c.setCourseName(resultSet.getString("Name"));
		c.setSection(resultSet.getString("Section"));
		c.setTimes(resultSet.getString("MeetingTime"));
		c.setLocation(resultSet.getString("Location"));
		return c;
	}
}